USE [NANS21V]
GO

/****** オブジェクト: SqlProcedure [dbo].[TeamInfo] スクリプト日付: 2014/05/27 11:31:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TeamInfo]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[TeamInfo];
GO

CREATE PROCEDURE [dbo].[TeamInfo]
	@TAIKAINO int ,
	@KYOGISYANO int
AS

SELECT 
	T.KYOGISYANO, 
	T.KYOGISYAMEI, 
	T.KYOGISYAKANA, 
	T.KYOGISYARYAKU, 
	T.KYOGISYASEISIKI, 
	T.SYOZOKUNO1, 
	SK.KYOGINO, 
	dbo.Kiroku(SK.KIROKU,SM.SYUMOKUKUBUN,SM.KIROKUKUBUN,SK.KIROKUFLG) AS KIROKU,
	SK.KIROKU, 
	SK.OPENFLG, 
	SK.KIROKUFLG,
	K.KYOGIMEI
FROM 
	(SELECT 
		KS.TAIKAINO, 
		KS.KYOGISYANO, 
		KS.KYOGISYAMEI, 
		KS.KYOGISYAKANA, 
		KS.KYOGISYARYAKU, 
		KS.KYOGISYASEISIKI, 
		KS.SYOZOKUNO1 
	FROM 
		TBL_KYOGISYA AS KS 
	WHERE 
		KS.TAIKAINO = @TAIKAINO 
		AND KS.KYOGISYAFLG = 1 ) 
	AS T, 
	TBL_SANKAKYOGI AS SK, 
	TBL_KYOGI AS K,
	TBL_SYUMOKU_MASTER AS SM
WHERE 
	T.TAIKAINO = SK.TAIKAINO 
	AND T.TAIKAINO = SK.TAIKAINO 
	AND T.KYOGISYANO = SK.KYOGISYANO 
	AND T.TAIKAINO = @TAIKAINO 
	AND T.KYOGISYANO = @KYOGISYANO 
	AND SK.TAIKAINO = K.TAIKAINO 
	AND SK.KYOGINO = K.KYOGINO 
	AND K.SYUMOKUNO = SM.SYUMOKUNO
	AND K.GATTAIFLG <> 2